/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sv.com.intesal.compra.sql;

/**
 *
 * @author ejerez
 */
public class SQLCXP {

    public static final String getCXP_Enca =
            " Select p.empresa_p,p.id_p, "
            + " COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0) as total_compra, " 
            + " ( select COALESCE(sum(ac.monto_ac),0) from abono_compra as ac where ac.id_c=c.id_c ) as total_abono_compra, "
            + " ( select COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) from devolucion_compra as dc, deta_devolucion_compra ddc where dc.codigo_dc=ddc.codigo_dc and dc.id_c=c.id_c ) as total_dc , "
            + " (COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0)-  ( select COALESCE(sum(ac.monto_ac),0) from abono_compra as ac where ac.id_c=c.id_c ) -  ( select COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) from devolucion_compra as dc, deta_devolucion_compra ddc where dc.codigo_dc=ddc.codigo_dc and dc.id_c=c.id_c )) as total_pendiente "
            + "   from enca_compra as c  inner join orden_compra as oc on oc.id_oc=c.id_oc  "
            + "   inner join proveedor as p on p.id_p=oc.id_p "
            + "   inner join deta_compra as d on c.id_c=d.id_c "
            + "  where c.estado_c='AC' and c.estado_pagado_c='P' ";

    public static final String getCXP_Deta =
            " Select COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0) as total_compra, "
            + " ( select COALESCE(sum(ac.monto_ac),0) from abono_compra as ac where ac.id_c=c.id_c ) as total_abono_compra, "
            + " ( select COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) from devolucion_compra as dc, deta_devolucion_compra ddc where dc.codigo_dc=ddc.codigo_dc and dc.id_c=c.id_c ) as total_dc , "
            + " (COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0)-  ( select COALESCE(sum(ac.monto_ac),0) from abono_compra as ac where ac.id_c=c.id_c ) -  ( select COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) from devolucion_compra as dc, deta_devolucion_compra ddc where dc.codigo_dc=ddc.codigo_dc and dc.id_c=c.id_c )) as total_pendiente, "
            + " (c.fecha_c+c.plazo_c) as vencimiento,( (c.fecha_c+c.plazo_c)-current_date) as dias_restante,  c.fecha_c,c.plazo_c, p.empresa_p,c.factura_c,c.tipo_c  "
            + "  from enca_compra as c  inner join orden_compra as oc on oc.id_oc=c.id_oc  "
            + "   inner join proveedor as p on p.id_p=oc.id_p "
            + "   inner join deta_compra as d on c.id_c=d.id_c "
            + "  where c.estado_c='AC' and c.estado_pagado_c='P' ";

    public static final String getWhereCondition = " and p.id_p=? ";
    public static final String getGroupBy = " group by c.id_c,c.fecha_c,c.plazo_c, p.empresa_p,c.factura_c,c.tipo_c  ";
    public static final String getCXP_Deta_Totales =
            " Select COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0) as total_compra, "
            + " COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) as total_dc, "
            + " COALESCE(sum(ac.monto_ac),0) as total_abono_compra, "
            + " (COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0)- "
            + " COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0)- "
            + " COALESCE(sum(ac.monto_ac),0)) as total_pendiente "
            + " from enca_compra as c "
            + " inner join orden_compra as oc on oc.id_oc=c.id_oc "
            + " inner join proveedor as p on p.id_p=oc.id_p "
            + " inner join deta_compra as d on c.id_c=d.id_c "
            + " left join devolucion_compra as dc on c.id_c=dc.id_c  "
            + " left join deta_devolucion_compra as ddc on dc.codigo_dc=ddc.codigo_dc "
            + " left join abono_compra as ac on c.id_c=ac.id_c	 "
            + " where c.estado_c='AC' and c.estado_pagado_c='P' "
            + " and p.id_p=? ";
    /*

     *
     *  Devuleve producto de plano cartesiano en DEVOLUCION_COMPRA y ABONO_COMPRA no se porque
    public static final String getCXP_Deta =
    " Select COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0) as total_compra, "
    + " COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0) as total_dc, "
    + " COALESCE(sum(ac.monto_ac),0) as total_abono_compra, "
    + " (COALESCE(sum( (d.cantidad_d*d.costo_d) + d.iva_d + d.exento_d),0)- "
    + " COALESCE(sum( (ddc.cantidad_d*ddc.costo_d) + ddc.iva_d + ddc.exento_d),0)- "
    + " COALESCE(sum(ac.monto_ac),0)) as total_pendiente, "
    + " (c.fecha_c+c.plazo_c) as vencimiento,( (c.fecha_c+c.plazo_c)-current_date) as dias_restante, "
    + " c.fecha_c,c.plazo_c, p.empresa_p,c.factura_c "
    + " from enca_compra as c "
    + " inner join orden_compra as oc on oc.id_oc=c.id_oc "
    + " inner join proveedor as p on p.id_p=oc.id_p "
    + " inner join deta_compra as d on c.id_c=d.id_c "
    + " left join devolucion_compra as dc on c.id_c=dc.id_c  "
    + " left join deta_devolucion_compra as ddc on dc.codigo_dc=ddc.codigo_dc "
    + " left join abono_compra as ac on c.id_c=ac.id_c	 "
    + " where c.estado_c='AC' and c.estado_pagado_c='P' "
    + " and p.id_p=? "
    + " group by c.id_c,c.fecha_c,c.plazo_c, p.empresa_p,c.factura_c ";

     */
}
